package com.huabo.contract.oracle.mapper;

import com.hbfk.entity.TblAttachment;
import com.hbfk.util.PageInfo;
import com.huabo.contract.oracle.entity.TblContractPlannode;

import net.sf.json.JSONObject;

import com.baomidou.mybatisplus.core.mapper.BaseMapper;
import org.apache.ibatis.annotations.*;

import java.math.BigDecimal;
import java.util.List;

/**
 * <p>
 *  Mapper 接口
 * </p>
 *
 * @author huabo
 * @since 2022-03-14
 */
public interface TblContractPlannodeMapper extends BaseMapper<TblContractPlannode> {




    @SelectProvider(type=TblContractPlannodeMapperSqlConfig.class,method="findPlanNodeListForCollection")
    List<TblContractPlannode> findPlanNodeListForCollection(PageInfo<TblContractPlannode> pageInfo, TblContractPlannode node);

    @SelectProvider(type=TblContractPlannodeMapperSqlConfig.class,method="selectCountByPageInfo")
    Integer selectCountByPageInfo(TblContractPlannode node);

    @Select("SELECT TCP.NODEID,TCP.NODECONTENT,TCP.PROJECTID,TCP.PLANSTARTDATE,TCP.PLANENDDATE,TCP.NODEPOST,TCP.NODEPLANPAYDATE,TCP.DISPATCHSTAFF as jbstaffid,TS.REALNAME,TCP.DISPATCHDEPT as jbunitid,ORG.ORGNAME,TCP.NODEMONEY,PLANNODESTATUS FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_STAFF TS ON TCP.DISPATCHSTAFF = TS.STAFFID LEFT JOIN TBL_ORGANIZATION ORG ON TCP.DISPATCHDEPT = ORG.ORGID WHERE TCP.PROJECTID = #{contractid}  ORDER BY TCP.NODEID ")
    List<TblContractPlannode> findPlanNodeListByContractId(BigDecimal contractid);

    @SelectProvider(type = TblContractPlannodeMapperSqlConfig.class,method = "findPlanNodeListForPayment")
    List<TblContractPlannode> findPlanNodeListForPayment(PageInfo<TblContractPlannode> pageInfo, TblContractPlannode node);

    @SelectProvider(type = TblContractPlannodeMapperSqlConfig.class,method = "findPlanNodeListForPaymentCount")
    Integer findPlanNodeListForPaymentCount(TblContractPlannode node);

//    @Select("SELECT SUM(NODEMONEY) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = #{contractId}")
//    BigDecimal findeSumMoneyByContractId(Integer contractId);

    @InsertProvider(type = TblContractPlannodeMapperSqlConfig.class,method = "saveContractPlannode")
    @Options(useGeneratedKeys=true, keyProperty="nodeid", keyColumn="NODEID")
    void saveContractPlannode(TblContractPlannode node);

    @SelectProvider(type = TblContractPlannodeMapperSqlConfig.class,method = "findeSumMoneyByContractId")
    BigDecimal findeSumMoneyByContractId(Integer contractId, BigDecimal nodeid);

    @UpdateProvider(type = TblContractPlannodeMapperSqlConfig.class,method = "updateContractPlannode")
    void updateContractPlannode(TblContractPlannode node);

    @Delete("DELETE FROM TBL_CONTRACT_PLANNODE WHERE NODEID = #{nodeId}")
    void removeContractPlannode(Integer nodeId);

    @Select("SELECT COUNT(NODEID) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = #{contractId}")
    Integer countPlanByContractId(BigDecimal contractId);

    @Select("SELECT SUM(NODEPOST) FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = #{contractId}")
    Integer sumPlanPostPoint(BigDecimal contractId);

    @SelectProvider(type = TblContractPlannodeMapperSqlConfig.class,method = "findContractNodeListByPageInfo")
    List<TblContractPlannode> findContractNodeListByPageInfo(PageInfo<TblContractPlannode> pageInfo);

    @SelectProvider(type = TblContractPlannodeMapperSqlConfig.class,method = "findCountContractNodeByPageInfo")
    Integer findCountContractNodeByPageInfo(PageInfo<TblContractPlannode> pageInfo);

    @Update("UPDATE TBL_CONTRACT_PLANNODE SET PLANNODESTATUS = #{planStatus},feedback= #{feedback} WHERE NODEID = #{nodeId}")
    void modifyPlanNodeStatus(Integer nodeId, Integer planStatus, String feedback);

    @Select("SELECT TCP.NODEID,TCP.NODECONTENT,TCP.PROJECTID,TCP.PLANSTARTDATE,TCP.PLANENDDATE,TCP.NODEPOST,TCP.NODEPLANPAYDATE,TCP.DISPATCHSTAFF,TS.REALNAME,TCP.DISPATCHDEPT,ORG.ORGNAME,TCP.nodemoney FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_STAFF TS ON TCP.DISPATCHSTAFF = TS.STAFFID LEFT JOIN TBL_ORGANIZATION ORG ON TCP.DISPATCHDEPT = ORG.ORGID WHERE TCP.NODEID = #{nodeId}")
    TblContractPlannode findBynodeId(Integer nodeId);

    @Select("SELECT * FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = #{contractId}")
    List<TblContractPlannode> getListPlannode(String contractId);

    @Select("SELECT * FROM TBL_CONTRACT_PLANNODE WHERE PROJECTID = #{contractId} AND (PLANNODESTATUS != 2 OR PLANNODESTATUS IS NULL)")
    List<TblContractPlannode> getListPlannodeForBianGeng(String contractId);
    
    
    @Select("SELECT TCP.NODEID,TCP.NODECONTENT,TCP.PROJECTID,TCP.PLANSTARTDATE,TCP.PLANENDDATE,TCP.NODEPOST,TCP.NODEPLANPAYDATE,TCP.DISPATCHSTAFF,TS.REALNAME,TCP.DISPATCHDEPT,ORG.ORGNAME,TCP.nodemoney FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_STAFF TS ON TCP.DISPATCHSTAFF = TS.STAFFID LEFT JOIN TBL_ORGANIZATION ORG ON TCP.DISPATCHDEPT = ORG.ORGID WHERE TCP.NODEID = #{nodeId}")
    TblContractPlannode selectByPlanId(Integer nodeId);

    @Select("SELECT TCP.NODEID,TCP.NODECONTENT,TCP.PROJECTID ,TCP.PLANSTARTDATE ,TCP.PLANENDDATE ,TCP.NODEPOST ,TCS.SPNODEID AS TCPSPNODEID,TCS.STARTDATE AS TCSSTARTDATE,TCS.ENDDATE AS TCSENDDATE,TCS.NODEMEMO AS TCSNODEMEMO,TCS.NODECONTENT AS TCSNODECONTENT,TCS.NODEPOST AS TCSNODEPOST,TCS.NODEMONEY AS TCSNODEMONEY,TCS.NODEFINISHDATE AS TCSNODEFINISHDATE,TCU.DCTYPE AS TCUDCTYPE,TCP.NODEPLANPAYDATE,TCS.ISWY FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID LEFT JOIN TBL_CONTRACT_SPNODE TCS ON TCP.NODEID = TCS.NODEID WHERE TCP.NODEID = #{planId}")
    TblContractPlannode findWriteContractPlanNode(Integer planId);

    @Select("SELECT ATTID,ATTNAME,ATTPATH,ATTSIZE,UPLOADER from TBL_ATTACHMENT WHERE ATTID IN (SELECT ATTID FROM TBL_CONTRACTNODE_ATT WHERE ATTTYPE = 1 AND NODEID = #{planId})")
    List<TblAttachment> findeWriteContractPlanFileInfo(Integer planId);

    @Select("SELECT * FROM TBL_CONTRACT_PLANNODE WHERE NODEID = #{nodeId}")
    TblContractPlannode findPlannodeById(BigDecimal nodeid);
    
    
    //@Select("SELECT TCP.NODEID,TCP.NODECONTENT,TCP.PROJECTID,TCP.PLANSTARTDATE,TCP.PLANENDDATE,TCP.NODEPOST,TCP.NODEMONEY  FROM TBL_CONTRACT_PLANNODE TCP LEFT JOIN TBL_STAFF TS ON TCP.DISPATCHSTAFF = TS.STAFFID LEFT JOIN TBL_ORGANIZATION ORG ON TCP.DISPATCHDEPT = ORG.ORGID WHERE TCP.PROJECTID = #{contractid}  ORDER BY TCP.NODEID ")
	@Select("SELECT TCP.NODEID,TCP.NODECONTENT,TCP.NODEPOST,TCP.NODEMONEY,to_char(TCP.PLANSTARTDATE,'yyyy-MM-dd') as PLANSTARTDATE,to_char(TCP.PLANENDDATE,'yyyy-MM-dd') as PLANENDDATE,"
			+ " CASE WHEN DCTYPE = '收款' THEN	 (SELECT SUM(INVOICEMONEY) FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEID IN (SELECT INVOICEID FROM TBL_CONTRACT_COLLECTION WHERE NODEID = TCP.NODEID)) "
			+ " WHEN DCTYPE = '付款' 	THEN   (SELECT SUM(INVOICEMONEY) FROM TBL_CONTRACT_INVOICESMANAGEMEN WHERE INVOICEID IN (SELECT INVOICEID FROM TBL_CONTRACT_PAYMENT WHERE NODEID = TCP.NODEID)) "
			+ " END AS yfnodemoney  FROM TBL_CONTRACT_PLANNODE TCP "
			+ " LEFT JOIN TBL_CYHW_UNIT TCU ON TCP.PROJECTID = TCU.CONTRACTID AND TCU.RECORDTYPE IN ('HTGL002','HTGL005') "
			+ "  WHERE TCP.PROJECTID = #{contractid} and PLANNODESTATUS='2' ORDER BY PLANSTARTDATE ASC ")
    List<JSONObject> findPlanNodeListById(BigDecimal contractid);


}
